package com.presalept.manipulatedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.presalept.polodo.ProjectDO;
import com.presalept.polodo.ProjectMemberDO;

import net.sf.json.*;

public class RetrieveProjectdata {
	private String dbJson;
	private String dbAllJson;
	private String dbOneJson;
	private List<ProjectDO> ProjectList;
	private List<ProjectDO> AllProjectList;
	
	//Get the list of all projects of inputting userid
	
	public String GetProjectList(int Userid) throws SQLException{
		
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select project.*, relationofprojectanduser.userid, userinfo.username, relationofprojectanduser.purelationstatus"
			+ " from project, relationofprojectanduser where project.createrid = ?";
		
		try{
			//建立数据库连接，获取特定用户的项目集合；
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbPreStmt.setInt(1, Userid);
			
			dbRs = dbPreStmt.executeQuery();	
			
			ProjectList = CreateProjectlist(dbRs);
			
			dbJson = CreateProjectlistJson(ProjectList);			
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbJson;
	}
	
	//create the projectlist
	private List<ProjectDO> CreateProjectlist(ResultSet dbRs) throws SQLException{
		
		List<ProjectDO> projectlist = new ArrayList<ProjectDO>();
		
		while(dbRs.next()){
			ProjectDO projectdo = new ProjectDO();
			projectdo.setProjectID(dbRs.getInt("projectid"));
			projectdo.setProjectName(dbRs.getString("projectname"));
			projectdo.setProjectDescription(dbRs.getString("projectdescription"));
			projectdo.setClientidofproject(dbRs.getInt("clientidofproject"));
			projectdo.setCreaterId(dbRs.getInt("createrid"));
			projectdo.setCreateDate(dbRs.getString("createdate"));
			projectdo.setUpdatestatus(dbRs.getInt("updatestatus"));
			projectdo.setLastupdatecontent(dbRs.getString("lastupdatecontent"));
			projectdo.setLastupdatecontent(dbRs.getString("lastupdatedate"));
			
			projectlist.add(projectdo);	
		}	
		
		return projectlist;
	}
	
	//create one project
	
	private ProjectDO CreateOneProject(ResultSet dbRs) throws SQLException{
		
		ProjectDO projectdo = new ProjectDO();
		
		while(dbRs.next()){
		projectdo.setProjectID(dbRs.getInt("projectid"));
		projectdo.setProjectName(dbRs.getString("projectname"));
		projectdo.setProjectDescription(dbRs.getString("projectdescription"));			
		projectdo.setClientidofproject(dbRs.getInt("clientidofproject"));
		projectdo.setCreaterId(dbRs.getInt("createrid"));
		projectdo.setCreateDate(dbRs.getString("createdate"));
		projectdo.setUpdatestatus(dbRs.getInt("updatestatus"));
		projectdo.setLastupdatecontent(dbRs.getString("lastupdatecontent"));
		projectdo.setLastupdatecontent(dbRs.getString("lastupdatedate"));
		
		ProjectMemberDO prmdo = new ProjectMemberDO();
		prmdo.setUserId(dbRs.getInt("userid"));
		prmdo.setUserName(dbRs.getString("username"));
		projectdo.getProjectMember().add(prmdo);
		
		}
		return projectdo;
	}
	
	//get all projects 
	
	public String GetAllProjectList() throws SQLException{
		
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from project";
		
		try{
	
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbRs = dbPreStmt.executeQuery();	
			
			AllProjectList = CreateProjectlist(dbRs);
			
			dbAllJson = CreateProjectlistJson(AllProjectList);		
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbAllJson;
	}

	//get one project
	
	public String GetOneProject(int Projectid)throws SQLException{
		
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from project where projectid = ?";
		
		try{
	
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbPreStmt.setInt(1, Projectid);
			
			dbRs = dbPreStmt.executeQuery();	
			
			dbOneJson = CreateProjectJson(CreateOneProject(dbRs));		
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbOneJson;
	}
	//create the JASON of the projectlist
	
	private String CreateProjectlistJson(List<ProjectDO> projectlist){
			
			/*dbJasonAll = listPmpdAll.get(0).getUserName() + "  " +
					listPmpdAll.get(0).getProjectName() + "  " +
				listPmpdAll.get(0).getlistEvent().get(0).getCreateDate().toString();*/
		JSONArray jsonObject = JSONArray.fromObject(projectlist);
		return jsonObject.toString();
		
	}
	
	private String CreateProjectJson(ProjectDO projectdo){
		
		/*dbJasonAll = listPmpdAll.get(0).getUserName() + "  " +
				listPmpdAll.get(0).getProjectName() + "  " +
			listPmpdAll.get(0).getlistEvent().get(0).getCreateDate().toString();*/
		JSONObject jsonObject = JSONObject.fromObject(projectdo);
		return jsonObject.toString();
	
	}	

}
